package com.durablemethods.futures.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import com.durablemethods.futures.client.NHLTraderPosition;
import com.durablemethods.futures.client.NHLTraderPositionService;
import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class NHLTraderPositionServiceImpl extends RemoteServiceServlet implements NHLTraderPositionService {
	
	private static final long serialVersionUID = -6518644882868927282L;
	private static final String CONNECTION_STRING = "jdbc:google:rdbms://durablemethods.com:duravest:durable-futures-db";
	private Logger log = Logger.getLogger(NHLTraderPositionServiceImpl.class.getName());

	@Override
	public List<NHLTraderPosition> getPositionsForTrader(String traderGoogleId) {
		
		List<NHLTraderPosition> positions = new ArrayList<NHLTraderPosition>();
		Connection c = null;
	      try {	    	 
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("select tr.TRADER_ID, tr.TEAM_ID, sum(tr.quantity) as TRADER_POSITION from durable_futures.TRADES tr,durable_futures.TEAMS t  where tr.TRADER_ID=? and t.id = tr.team_id and t.status='A' group by tr.TRADER_ID, tr.TEAM_ID  order by t.standings_position,t.id,t.conference");
			ps.setString(1, traderGoogleId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				NHLTraderPosition position = new NHLTraderPosition();
				position.setTeam(rs.getString("TEAM_ID"));
				position.setPosition(rs.getInt("TRADER_POSITION"));
				positions.add(position);
			}
			ps.close();
			c.close();
		} catch (SQLException e) {
			log.info(e.getMessage());
			e.printStackTrace();
		}
		return positions;
	}

}
